I made a test class against the repository methods shown below:

        public void AddFile<TFileType>(TFileType FileToAdd) where TFileType : File
  {
   try
   {
    _session.Save(FileToAdd);
    _session.Flush();
   }
   catch (Exception e)
   {
    if (e.InnerException.Message.Contains("Violation of UNIQUE KEY"))
     throw new ArgumentException("Unique Name must be unique");
    else
     throw e;
   }
  }

        public void RemoveFile(File FileToRemove)
  {
   _session.Delete(FileToRemove);
   _session.Flush();
  }

And the test class:

            try
   {
    Data.File crashFile = new Data.File();
    crashFile.UniqueName = "NonUniqueFileNameTest";
    crashFile.Extension = ".abc";
    repo.AddFile(crashFile);
    Assert.Fail();
   }
   catch (Exception e)
   {
    Assert.IsInstanceOfType(e, typeof(ArgumentException));
   }

   // Clean up the file
   Data.File removeFile = repo.GetFiles().Where(f => f.UniqueName == "NonUniqueFileNameTest").FirstOrDefault();
   repo.RemoveFile(removeFile);

The test fails. When I step in to trace the problem, I found out that when I do the _session.flush() right after _session.delete(), it throws the exception, and if I look at the sql it does, it is actually submitting a "INSERT INTO" statement, which is exactly the sql that cause UNIQUE CONSTRAINT error. I tried to encapsulate both in transaction but still same problem happens. Anyone know the reason?